package com.metagurukul.informer.dao;

import java.sql.*;
import java.util.ArrayList;
import com.metagurukul.informer.utility.ConnectionFactory;
import com.metagurukul.informer.valueObjects.*;

/**
 * Class TagDAO for providing the result for tags from the database
 * @author 
 */
public class TagDAO {
	   
	/**
	 * ...Method to find TagId by TagName and if Tag does not exists insert into tags table
	 * @param tag name 
	 * @return tag id 
	 */
	public int findTagId(String temp) throws SQLException {
		Connection	con= ConnectionFactory.getInstance();
		int tag_id=0;
		
			String sql="SELECT id FROM tags where name=?";
			PreparedStatement ps = null;
			ps = con.prepareStatement(sql);
		   	ps.setString(1,temp);
			ResultSet rs= ps.executeQuery();
			/*
			 * checks @param temp(tag name) exists or not in the database
			 */
			 if(rs.next()){
				 tag_id = rs.getInt(1);
			 }else{
				 /*
				  * if tag does not exists insert it into database
				  */
				  sql = "insert into tags(name) values(?)";
   			   	  ps = con.prepareStatement(sql);
				  ps.setString(1,temp);
				  int result = ps.executeUpdate();
				  //System.out.println(result + " rows updated");
				  sql = "SELECT id FROM tags where name=?";
				  ps = con.prepareStatement(sql);
				  ps.setString(1,temp);
				  rs= ps.executeQuery();
				  rs.next();
				  tag_id=rs.getInt(1);
			 }
		
			if(ps!=null){
				 ps.close();
			}
			if(rs!=null) {
				rs.close();
			}
				   	
			if(con!=null){
				con.close();
			}
					 
		return tag_id;
	}
	
   /**
    * ...Method to find TagName by TagId
    * @param tag id 
    * @return tag name 
    */
	public String findByTagId(int id)  throws SQLException{
		Connection	con= ConnectionFactory.getInstance();
		String tag_name=null;
		
			/*
	   		 * SQL query for finding out the tag name related to the tag id
	   		 */
			String sql="SELECT * FROM tags where id=?";
			PreparedStatement ps = null;
			ps = con.prepareStatement(sql);
		   	ps.setInt(1,id);
			ResultSet rs= ps.executeQuery();
		    if(rs.next()){
		    	tag_name = rs.getString(2);
		    }
		    
		    if(ps!= null){
		    	ps.close();
		    }
		    if(rs!= null){
		    	rs.close();
		    }
		    if(con!= null){
		    	con.close();
		    }
		return tag_name;
	}
	
	/**
	 * method for get all tags from database
	 * @return list of tags objects
	 */
	public ArrayList<TagQuestionCounter> showTags()  throws SQLException{
		Connection	con= ConnectionFactory.getInstance();
		TagQuestionCounter tags = null;
		ArrayList<TagQuestionCounter> listOfTags = new ArrayList<TagQuestionCounter>();
		
			String query = "SELECT id,name,count(*) FROM tags JOIN question_tag_mapping ON tags.id=question_tag_mapping.tag_id group by id";
   			Statement stm = con.createStatement();
   			ResultSet rs=stm.executeQuery(query);
   			while(rs.next()) {
				tags = new TagQuestionCounter(rs.getInt("id"), rs.getString("name"),rs.getInt(3));
				listOfTags.add(tags);   			
   			}
		
		    if(stm!= null){
		    	stm.close();
		    }
		    if(rs!= null){
		    	rs.close();
		    }
		    if(con!= null){
		    	con.close();
		    }
   			
		return listOfTags;		
	}
	
	/**
	 * method for get all tags from database for a associated question_id
	 * @return list of tags objects
	 */
	public ArrayList<Tags> showTagsByQuestionId(int question_id) throws SQLException {
		Tags tags = null;
		ArrayList<Tags> listOfTags = new ArrayList<Tags>();
		
			Connection con = ConnectionFactory.getInstance();	
			String query = "SELECT * FROM tags T,question_tag_mapping Q WHERE T.id=Q.tag_id AND Q.question_id="+question_id;
   			Statement stm = con.createStatement();
   			ResultSet rs=stm.executeQuery(query);
   			while(rs.next()) {
				tags = new Tags(rs.getInt("id"), rs.getString("name"));
				listOfTags.add(tags);
   			}
		
		    if(stm!= null){
		    	stm.close();
		    }
		    if(rs!= null){
		    	rs.close();
		    }
		    if(con!= null){
		    	con.close();
		    }
   			
		return listOfTags;		
	}
	public String findByTagName(String tagName) throws SQLException {
		String tagList="";
		Connection con = ConnectionFactory.getInstance();	
		String sql = "SELECT name FROM tags WHERE name LIKE '%"+tagName.trim()+"%'";
		Statement stm = con.createStatement();
		ResultSet rs=stm.executeQuery(sql);
		while (rs.next ()){
			tagList+="<li onClick=\"go('"+rs.getString("name")+"');\">"+rs.getString("name")+"</li>";
			}
		if(stm!= null){
	    	stm.close();
	    }
	    if(rs!= null){
	    	rs.close();
	    }
	    if(con!= null){
	    	con.close();
	    }
		return tagList;		
	}
	
}
